package quiz;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashSet;

import quiz.MyDBInfo;

public class DataBase {
	private Connection con;

	/**
	 * DataBase() -
	 * Initializes a connection with database information from MyDBInfo.java
	 */
	public DataBase() {
		try { Class.forName("com.mysql.jdbc.Driver"); }
		catch (ClassNotFoundException e) {   }
	}





	/* =============== DATABASE FUNCTIONS =============== */




	/**
	 * Opens a connection to the Database using information from MyDBInfo.java.
	 * Need to call this to run other methods
	 * Should be called when website opens.
	 */
	public void open() {
		if(!isOpen()) {
			try {
				con = DriverManager.getConnection
				( "jdbc:mysql://" + MyDBInfo.MYSQL_DATABASE_SERVER, MyDBInfo.MYSQL_USERNAME ,MyDBInfo.MYSQL_PASSWORD);
			} catch(SQLException e) {   }
		}
	}

	/**
	 * Closes the connection to the Database. Should be called when website closes.
	 */
	public void close() {
		try { if(con != null) con.close(); }
		catch (SQLException e) {   }
		con = null;
	}

	/**
	 * Method that returns whether the database connection is open or not
	 * @return boolean - whether the database is open
	 */
	public boolean isOpen() {
		if (con == null) return false;
		try {
			return !con.isClosed();
		} catch (SQLException e) {
			return false;
		}
	}

	/**
	 * Method that returns a ResultSet containing everything you queried for
	 * @param String - String representation of query. Make sure to sanitize
	 * query string to maintain integrity of database
	 * @return ResultSet - resultset of given query, null if query failed
	 */
	public ResultSet query(String query) {
		try {
			open();
			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery(query);
			return rs;
		} catch(SQLException e) { return null; }
	}

	/**
	 * Resets all tables by first dropping and then re-adding them.
	 * Used for debugging purposes. Use with caution, this cannot be undone.
	 */
	public void reset() {
		try {
			open();
			Statement stmt = con.createStatement();
			stmt.execute("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			stmt.execute("DROP TABLE IF EXISTS accounts");
			stmt.execute("DROP TABLE IF EXISTS quizzes");
			stmt.execute("DROP TABLE IF EXISTS questions");
			stmt.execute("DROP TABLE IF EXISTS answers");
			stmt.execute("DROP TABLE IF EXISTS messages");
			stmt.execute("DROP TABLE IF EXISTS achievements");
			stmt.execute("DROP TABLE IF EXISTS friends");						
			stmt.execute("DROP TABLE IF EXISTS results");
			stmt.execute("DROP TABLE IF EXISTS tags");
			stmt.execute("CREATE TABLE accounts (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64), username VARCHAR(64), password_hash VARCHAR(64), is_admin BOOLEAN)");
			stmt.execute("CREATE TABLE quizzes (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(64),description VARCHAR(512),is_ordered BOOLEAN,is_singlePage BOOLEAN,has_practiceMode BOOLEAN,immediate_correction BOOLEAN,visible_to_public BOOLEAN,creatorID INT,date_created TIMESTAMP)");
			stmt.execute("CREATE TABLE questions (id INT PRIMARY KEY AUTO_INCREMENT,quizID INT,content VARCHAR(512),type TINYINT(1),time INT,priority INT, weight INT)");
			stmt.execute("CREATE TABLE answers (id INT PRIMARY KEY AUTO_INCREMENT,questionID INT,content VARCHAR(256),type TINYINT(1),is_correct BOOLEAN,priority INT)");
			stmt.execute("CREATE TABLE messages (id INT PRIMARY KEY AUTO_INCREMENT,fromID INT,toID INT,type INT,content VARCHAR(512),is_read BOOLEAN,quizID INT,time_sent TIMESTAMP)");
			stmt.execute("CREATE TABLE achievements (id INT PRIMARY KEY AUTO_INCREMENT,userID INT,achievementID INT)");
			stmt.execute("CREATE TABLE friends (id INT PRIMARY KEY AUTO_INCREMENT,userID INT,friendID INT)");
			stmt.execute("CREATE TABLE results (id INT PRIMARY KEY AUTO_INCREMENT,userID INT,quizID INT,score INT,time INT,date_taken TIMESTAMP)");
			stmt.execute("CREATE TABLE tags (id INT PRIMARY KEY AUTO_INCREMENT,quizID INT,tag VARCHAR(512))");
			stmt.execute("INSERT INTO accounts(name, username, password_hash, is_admin) VALUES (\"Anonymous User\", \"Anonymous_User\", \"not_a_password_hash\", false);");
			stmt.execute("UPDATE accounts SET id=-1;");
		} catch (SQLException e) {
		}
	}

	/**
	 * Given a filename, it will parse an XML file of a quiz and store it
	 * in the database
	 */
	public void parseXML(String filename) {
		try {
			open();

			String file = "";
			BufferedReader br = new BufferedReader(new FileReader(filename));
			String line = br.readLine();
			while(line != null) {
				file += line;
				line = br.readLine();
			}

			//have xml string in file at this point
			Quiz quiz = readQuiz(file);

			String questions = file.substring(file.indexOf("<question"), file.lastIndexOf("</question>") + 11); //body of questions

			String question = getBetween(questions, "<question>", "</question>");
			int priority = 0;
			while(question != null) { //read every question
				readQuestion(quiz.getID(), question, ++priority);

				questions = questions.substring(questions.indexOf(question) + question.length());
				question = getBetween(questions, "<question>", "</question>");
			}

		} catch (Exception e) {
			
		}
	}

	private Quiz readQuiz(String file) {
		int creatorID = -1; //no creator
		boolean is_ordered = true;
		boolean is_singlePage = true;
		boolean has_practiceMode = false;
		boolean immediate_correction = false;
		boolean visible_to_public = true;
		String name = "";
		String description = "";


		String quiz_info = getBetween(file, "<quiz", ">");
		if(quiz_info.contains("random=\"true"))  is_ordered = false;
		if(quiz_info.contains("one-page=\"false"))  is_singlePage = false;
		if(quiz_info.contains("practice-mode=\"true")) has_practiceMode = true;
		if(quiz_info.contains("immediate-correction=\"true"))  immediate_correction = true;
		name = file.substring(file.indexOf("<title>") + 7, file.indexOf("</title>"));
		description = file.substring(file.indexOf("<description>") + 13, file.indexOf("</description>"));

		Quiz quiz = new Quiz(name, description, is_ordered, is_singlePage, has_practiceMode, immediate_correction, visible_to_public, creatorID);
		this.storeQuiz(quiz);

		//store tags
		String tag = getBetween(file, "<category>", "</category>");
		while(tag != null) {
			quiz.setTag(tag);

			file = file.substring(file.indexOf(tag) + tag.length());
			tag = getBetween(file, "<category>", "</category>");
		}

		return quiz;
	}

	private void readQuestion(int quizID, String file, int priority) {
		int time = 0;
		if(file.contains("type=\"question-response")) {
			int type = Question.QR;
			String content = getBetween(file, "<query>", "</query>");
			Question question = new Question(quizID, content, type, time, priority, 1);
			this.storeQuestion(question);

			String answer_text = getBetween(file, "<answer>", "</answer>");
			int ans_priority = 0;

			while(answer_text != null) {
				Answer answer = new Answer(question.getID(), answer_text, Answer.TEXT, true, ++ans_priority);
				this.storeAnswer(answer);

				file = file.substring(file.indexOf(answer_text) + answer_text.length());
				answer_text = getBetween(file, "<answer>", "</answer>");
			}
		}
		else if(file.contains("type=\"fill-in-blank")) {
			int type = Question.FITB;
			String content = getBetween(file, "<pre>", "</pre>") + "_" + getBetween(file, "<post>", "</post");

			Question question = new Question(quizID, content, type, time, priority, 1);
			this.storeQuestion(question);

			String answer_text = getBetween(file, "<answer>", "</answer>");
			int ans_priority = 0;

			while(answer_text != null) {

				Answer answer = new Answer(question.getID(), answer_text, Answer.TEXT, true, ++ans_priority);
				this.storeAnswer(answer);

				file = file.substring(file.indexOf(answer_text) + answer_text.length());
				answer_text = getBetween(file, "<answer>", "</answer>");
			}

		}
		else if(file.contains("type=\"multiple-choice")) {
			int type = Question.MC;
			String content = getBetween(file, "<query>", "</query>");
			Question question = new Question(quizID, content, type, time, priority, 1);
			this.storeQuestion(question);

			String answer_text = getBetween(file, "<option", "</option>");
			int ans_priority = 0;

			while(answer_text != null) {
				boolean is_correct = false;
				if(answer_text.contains("answer=\"answer\"")) {
					is_correct = true;
					answer_text = answer_text.substring(answer_text.indexOf(">") + 1);
				} else answer_text = answer_text.substring(1);

				Answer answer = new Answer(question.getID(), answer_text, Answer.TEXT, is_correct, ++ans_priority);
				this.storeAnswer(answer);

				file = file.substring(file.indexOf(answer_text) + answer_text.length());
				answer_text = getBetween(file, "<option", "</option>");
			}
		}
		else if(file.contains("type=\"picture-response")) {
			int type = Question.PICTURE;
			String content = getBetween(file, "<image-location>", "</image-location>");
			Question question = new Question(quizID, content, type, time, priority, 1);
			this.storeQuestion(question);

			String answers = "";
			if(file.contains("<answer-list>")) answers = getBetween(file, "<answer-list>", "</answer-list>");
			else answers = file.substring(file.indexOf("answ"));
			String answer_text = getBetween(answers, ">", "</answer>");
			int ans_priority = 0;
			while(answer_text != null) {
				Answer answer = new Answer(question.getID(), answer_text, Answer.TEXT, true, ++ans_priority);
				this.storeAnswer(answer);

				answers = answers.substring(answers.indexOf(answer_text) + answer_text.length() + "</answer>".length());
				answer_text = getBetween(answers, ">", "</answer>");
			}

		}		
	}

	private static String getBetween(String str, String start, String end) {
		int index = str.indexOf(start) + start.length();
		if(index == -1) return null;
		int endIndex = str.indexOf(end, index);
		if(endIndex == -1) return null;
		return str.substring(index , endIndex);
	}

	/* =============== DATABASE FUNCTIONS =============== */


	/* =============== GETTING FUNCTIONS =============== */





	/**
	 * Queries and returns account 
	 * @param id - int id number you want account for
	 * @return Account in database with matching id or null if there is no match
	 */
	public Account getAccount(int id) {
		Account account = null;
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM accounts WHERE id=" + id);
			rs.next();

			account = createAccount(rs);

		} catch (SQLException e) {
		}

		return account;
	}

	/**
	 * Queries and returns the Account with the given username.
	 * Can be used to check if a username is already in use.
	 * @param username - String username of Account desired
	 * @return Account with username or null if there is no match
	 */
	public Account getAccountByUsername(String username) {
		Account account = null;
		try {
			open();
			
			PreparedStatement stmt = con.prepareStatement("SELECT * FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".accounts WHERE username=?");
			stmt.setString(1, username);

			ResultSet rs = stmt.executeQuery();
			rs.next();

			account = createAccount(rs);

		} catch (SQLException e) {
		}

		return account;
	}
	
	public ArrayList<Account> getAccountsByQuery(String query) {
		HashSet<Account> accounts = new HashSet<Account>();
		try {
			PreparedStatement stmt = con.prepareStatement("SELECT * FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".accounts WHERE UPPER(name) LIKE UPPER(?) OR UPPER(username) LIKE UPPER(?)");
			stmt.setString(1, "%" + query + "%");
			stmt.setString(2, "%" + query + "%");
						
			ResultSet rs = stmt.executeQuery();
			
			if(!rs.isBeforeFirst()) {
				return new ArrayList<Account>();
			}
			
			while(rs.next()) {
				if(rs.getInt("id") < 0) continue;
				accounts.add(createAccount(rs));
			}
			
		} catch (SQLException e) {
		}

		return new ArrayList<Account>(accounts);
	}

	/**
	 * Gets a quiz from the database with specified quiz id
	 * @param id - int of desired quiz's id
	 * @return Quiz with id matching parameter or null if no quiz is there
	 */
	public Quiz getQuiz(int id) {
		Quiz quiz = null;
		try {
			open();
			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM quizzes WHERE id=" + id);
			rs.next();
			quiz = createQuiz(rs);
		} catch (SQLException e) {
		}
		return quiz;
	}

	/**
	 * Gets a question from the database with the specified question id
	 * @param id - int of desired question's id
	 * @return Question with specified id or null if no question with id can be found
	 */
	public Question getQuestion(int id) {
		Question question = null;
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM questions WHERE id=" + id);
			rs.next();
			question = createQuestion(rs);

		} catch (SQLException e) {
		}

		return question;
	}

	/**
	 * Gets an arraylist of quizzes given a userID
	 * @param userID -- id of user
	 * @return arraylist of quizzes that are owned by the user
	 */
	public ArrayList<Quiz> getQuizzesByUser(int userID) {
		ArrayList<Quiz> quizzes = new ArrayList<Quiz>();
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * from quizzes WHERE creatorID=" + userID + " ORDER BY date_created DESC");

			while(rs.next()) {
				quizzes.add(createQuiz(rs));
			}

		} catch(SQLException e) {
		}

		return quizzes;
	}

	/**
	 * Gets an arraylist of quizzes that were recently taken
	 * @param numResults -- number of results you want
	 * @return arraylist of quizzes that are recently taken
	 */
	public ArrayList<Quiz> getRecentlyTakenQuizzes(int userID, int numResults) {

		ArrayList<Quiz> quizzes = new ArrayList<Quiz>();
		ArrayList<Integer> friends = getFriendIDs(userID);
		
		try {
			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT quizID FROM results ORDER BY date_taken DESC LIMIT 0, " + numResults);

			if (!rs.isBeforeFirst() ) {    
				return quizzes;
			} 

			String query = "SELECT * FROM quizzes WHERE id IN (";
			query += userID + ", "; //handle yours
			while(rs.next()) {
				query += "" + rs.getInt("quizID") + ", ";
			}

			query = query.substring(0, query.length() - 2);
			query += ")";

			Statement stmt2 = con.createStatement();
			stmt2.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs2 = stmt2.executeQuery(query);

			while(rs2.next()) {
				if(rs2.getBoolean("visible_to_public") == false && !friends.contains(rs2.getInt("creatorID"))) continue;
				quizzes.add(createQuiz(rs2));
			}

		}
		catch (SQLException e) {
		}

		return quizzes;
	}
	
	public ArrayList<Quiz> getQuizzesByQuery(int userID, String query) {
		HashSet<Quiz> quizzes = new HashSet<Quiz>();
		ArrayList<Integer> friends = getFriendIDs(userID);
		try {

			PreparedStatement stmt = con.prepareStatement("SELECT * FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".quizzes WHERE UPPER(name) LIKE UPPER(?) OR UPPER(description) LIKE UPPER(?)");
			stmt.setString(1, "%" + query + "%");
			stmt.setString(2, "%" + query + "%");
			

			ResultSet rs = stmt.executeQuery();

			if (!rs.isBeforeFirst() ) {    
				return new ArrayList<Quiz>();
			} 

			while(rs.next()) {
				if(rs.getBoolean("visible_to_public") == false && !friends.contains(rs.getInt("creatorID")) && rs.getInt("creatorID") != userID) continue;
				quizzes.add(createQuiz(rs));
			}
			
			//get quizzes by tag
			
			quizzes.addAll(getQuizzesByTag(userID, query));

		}
		catch (SQLException e) {
		}
		return new ArrayList<Quiz>(quizzes);
	}

	/**
	 * Gets quizzes with certain tags
	 * @param query - search String for the tags
	 * @return ArrayList of quizzes matching tags
	 */
	public ArrayList<Quiz> getQuizzesByTag(int userID, String query) { //visible
		HashSet<Quiz> quizzes = new HashSet<Quiz>();
		ArrayList<Integer> friends = getFriendIDs(userID);
		try {

			PreparedStatement stmt = con.prepareStatement("SELECT quizID FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".tags WHERE UPPER(tag) LIKE UPPER(?)");
			stmt.setString(1, "%" + query + "%");
			

			ResultSet rs = stmt.executeQuery();

			if (!rs.isBeforeFirst() ) {    
				return new ArrayList<Quiz>();
			} 

			String query2 = "SELECT * FROM quizzes WHERE id IN (";
			query += userID + ", "; //handle yours
			while(rs.next()) {
				query2 += "" + rs.getInt("quizID") + ", ";
			}

			query2 = query2.substring(0, query2.length() - 2);
			query2 += ")";

			
			Statement stmt2 = con.createStatement();
			stmt2.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs2 = stmt2.executeQuery(query2);

			while(rs2.next()) {
				if(rs2.getBoolean("visible_to_public") == false && !friends.contains(rs2.getInt("creatorID"))) continue;
				quizzes.add(createQuiz(rs2));
			}

		}
		catch (SQLException e) {
		}
		return new ArrayList<Quiz>(quizzes);
	}

	/**
	 * Gets an arraylist of quizzes given a userID
	 * @param numResults -- number of results you want
	 * @return arraylist of quizzes that are recently taken
	 */
	public ArrayList<Quiz> getRecentlyCreatedQuizzes(int userID, int numResults) {

		ArrayList<Quiz> quizzes = new ArrayList<Quiz>();
		ArrayList<Integer> friends = getFriendIDs(userID);

		try {
			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM quizzes ORDER BY date_created DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				if(rs.getBoolean("visible_to_public") == false && !friends.contains(rs.getInt("creatorID")) && rs.getInt("creatorID") != userID) continue;
				quizzes.add(createQuiz(rs));
			}

		}
		catch (SQLException e) {
		}

		return quizzes;
	}

	/**
	 * Gets all the questions associated with a quiz using quiz
	 * @param quizID - int of desired quiz's id
	 * @return A sortable ArrayList of Questions of that specified quiz
	 */
	public ArrayList<Question> getQuizQuestions(int quizID) {
		ArrayList<Question> questions = new ArrayList<Question>();
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM questions WHERE quizID=" + quizID + " ORDER BY priority");

			while(rs.next()) {
				questions.add(createQuestion(rs));
			}


		} catch (SQLException e) {
			return null;
		}

		return questions;
	}

	/**
	 * Gets an Answer with a specific ID
	 * @param id - int representing id of desired answer
	 * @return Answer with specified id or null if non exist
	 */
	public Answer getAnswer(int id) {
		Answer answer = null;
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM answers WHERE id=" + id);
			rs.next();
			answer = createAnswer(rs);

		} catch (SQLException e) {
		}

		return answer;
	}

	/**
	 * Gets a sortable ArrrayList of answers for a given question
	 * @param questionID -- int id of desired question 
	 * @return ArrayList of Answers that consists of all the answers for the given question
	 */
	public ArrayList<Answer> getAnswersForQuestion(int questionID) {
		ArrayList<Answer> answers = new ArrayList<Answer>();
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM answers WHERE questionID=" + questionID);

			while(rs.next()) {
				answers.add(createAnswer(rs));
			}


		} catch (SQLException e) {
			return null;
		}
		return answers;
	}

	/**
	 * Gets a Message with given id
	 * @param id -- int id of desired Message
	 * @return Message with given id or null if none found
	 */
	public Message getMessage(int id) {
		Message message = null;
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM messages WHERE id=" + id);
			rs.next();

			message = createMessage(rs);

		} catch (SQLException e) {
		}

		return message;
	}

	/**
	 * Gets an ArrayList of messages that are labeled announcements
	 * @param numAnnouncements - an upperbound on how many announcements are returned
	 * @return ArrayList of Messages for given user. Null if query fails, empty if no announcements.
	 */
	public ArrayList<Message> getAnnoucements(int numResults) {
		ArrayList<Message> messages = new ArrayList<Message>();
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM messages WHERE type=" + Message.ANNOUNCEMENT  + " ORDER BY time_sent DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				messages.add(createMessage(rs));
			}

		} catch (SQLException e) {
			return null;
		}
		return messages;
	}

	/**
	 * Gets an ArrayList of messages for a certain user given a user
	 * @param userID - int id of Account you want to get messages from
	 * @return ArrayList of Messages for given user
	 */
	public ArrayList<Message> getUserSentMessages(int userID) {
		ArrayList<Message> messages = new ArrayList<Message>();
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM messages WHERE fromID=" + userID + " ORDER BY time_sent DESC");

			while(rs.next()) {
				messages.add(createMessage(rs));
			}

		} catch (SQLException e) {
			return null;
		}
		return messages;
	}
	
	public ArrayList<Message> getUserReceivedMessages(int userID) {
		ArrayList<Message> messages = new ArrayList<Message>();
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM messages WHERE toID=" + userID + " ORDER BY time_sent DESC");

			while(rs.next()) {
				messages.add(createMessage(rs));
			}

		} catch (SQLException e) {
			return null;
		}
		return messages;
	}


	/**
	 * Gets all the Achievements a User has, given a user.
	 * Returns the achievements in a ArrayList
	 * @param userID - int id of desired Account you want to get Achievements for
	 * @return ArrayList of Achievements, empty if there are no achievements, null if sql failed
	 */
	public ArrayList<Achievement> getUserAchievements(int userID) {
		HashSet<Achievement> achievements = new HashSet<Achievement>();
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM achievements WHERE userID=" + userID);

			while(rs.next()) {
				achievements.add(createAchievement(rs));
			}

		} catch (SQLException e) {
			return null;
		}
		return new ArrayList<Achievement>(achievements);
	}

	/**
	 * Gets all the Friends a User has, given a user
	 * Returns the friends (Account) in a ArrayList
	 * @param userID - int of id of Account desired to get friends from
	 * @return ArrayList of Accounts, empty if there are no friends, null if sql failed
	 */
	public ArrayList<Account> getFriends(int userID) {
		ArrayList<Integer> friendIDs = getFriendIDs(userID);
		ArrayList<Account> friends = new ArrayList<Account>();

		try {
			open();

			if(friendIDs.isEmpty()) return friends;
			String query = "SELECT * FROM accounts WHERE id IN (";
			for(int i = 0; i < friendIDs.size(); i++) {
				query += friendIDs.get(i) + ", ";
			}

			query = query.substring(0, query.length() - 2);
			query += ")";


			Statement stmt2 = con.createStatement();
			stmt2.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs2 = stmt2.executeQuery(query);

			while(rs2.next()) {
				Account account = createAccount(rs2);
				friends.add(account);
			}


		} catch (SQLException e) {
			return null;
		}
		return friends;
	}

	/**
	 * Gets a Result with a specific ID
	 * @param id - int representing id of desired result
	 * @return Result with specified id or null if non exist
	 */
	public Result getResult(int id) {
		Result result = null;
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results WHERE id=" + id);
			rs.next();

			result = createResult(rs);

		} catch (SQLException e) {
		}

		return result;
	}

	/**
	 * Gets a number of Results of quizzes for a user. If numResults is < 0, throws an exception
	 * If numResults < number of Results in database, it will return all the results in the database
	 * @param userID - int id of Account desired to get results for
	 * @return ArrayList of Results, empty if there are no results, null if sql failed
	 */
	public ArrayList<Result> getRecentResultsForUser(int userID, int numResults) {
		if(numResults <= 0) throw new RuntimeException("You are requesting a number of results <= 0");

		ArrayList<Result> results = new ArrayList<Result>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results WHERE userID=" + userID + " ORDER BY date_taken DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				Result result = createResult(rs);
				if(!result.wasPractice()) results.add(result);
			}


		} catch (SQLException e) {
			 
			return null;
		}

		return results;
	}

	/**
	 * Gets a number of global recent Results of all quizzes. If numResults is < 0, throws an exception
	 * If numResults < number of Results in database, it will return all the results in the database
	 * @param userID - String or int of id, or Account desired
	 * @return ArrayList of Results, empty if there are no results, null if sql failed
	 */
	public ArrayList<Result> getGlobalRecentResults(int numResults) {
		if(numResults <= 0) throw new RuntimeException("You are requesting a number of results <= 0");

		ArrayList<Result> results = new ArrayList<Result>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results ORDER BY date_taken DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				Result result = createResult(rs);
				if(!result.wasPractice()) results.add(result);
			}

		} catch (SQLException e) {
			return null;
		}

		return results;
	}

	/**
	 * Gets a number of global recent Results for a specific quiz given a quiz
	 * If numResults is < 0, throws an exception
	 * If numResults < number of Results in database, it will return all the results in the database
	 * @param userID - int id of Quiz desired to get results for
	 * @return ArrayList of Results, empty if there are no results, null if sql failed
	 */
	public ArrayList<Result> getTopQuizResults(int quizID, int numResults) {
		if(numResults <= 0) throw new RuntimeException("You are requesting a number of results <= 0");

		ArrayList<Result> results = new ArrayList<Result>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results WHERE quizID=" + quizID + " ORDER BY score DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				Result result = createResult(rs);
				if(!result.wasPractice()) results.add(result);
			}

		} catch (SQLException e) {
			return null;
		}
		
		Collections.sort(results, new Comparator<Result>() {
			public int compare(Result arg0, Result arg1) {
				if(arg0.getScore() != arg1.getScore()) return arg1.getScore() - arg0.getScore();
				return arg0.getTime() - arg1.getTime();
			}
		});

		return results;
	}

	public ArrayList<Quiz> getPopularQuizzes(int userID, int numResults) {
		if(numResults <= 0) throw new RuntimeException("You are requesting a number of results <= 0");
		ArrayList<Integer> friends = getFriendIDs(userID);
		ArrayList<Quiz> quizzes = new ArrayList<Quiz>();
		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT *, COUNT(quizID) FROM results GROUP BY quizID ORDER BY COUNT(quizID) DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				quizzes.add(getQuiz(rs.getInt("quizID")));
			}

		} catch(SQLException e) {
		}

		ArrayList<Quiz> q = new ArrayList<Quiz>();
		for(Quiz quiz : quizzes) {
			if(quiz.isVisible_to_public() == false && !friends.contains(quiz.getCreator())) continue;
			q.add(quiz);
		}
		
		return q;
	}
	
	public ArrayList<String> getPopularTags(int numResults) {
		HashSet<String> tags =  new HashSet<String>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT *, COUNT(tag) FROM tags GROUP BY tag ORDER BY COUNT(tag) DESC LIMIT 0, " + numResults);


			while(rs.next()) {
				tags.add(rs.getString("tag"));
			}

		} catch(SQLException e) {
		}

		return new ArrayList<String>(tags);
	}

	public ArrayList<String> getTags(int quizID) {
		HashSet<String> tags =  new HashSet<String>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM tags WHERE quizID=" + quizID);

			while(rs.next()) {
				tags.add(rs.getString("tag"));
			}

		} catch(SQLException e) {
		}

		return new ArrayList<String>(tags);
	}

	public ArrayList<Result> getTopQuizResultsInDay(int quizID, int numResults) {
		if(numResults <= 0) throw new RuntimeException("You are requesting a number of results <= 0");

		ArrayList<Result> results = new ArrayList<Result>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results WHERE quizID=" + quizID + " AND date_taken >= DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				Result result = createResult(rs);
				if(!result.wasPractice()) results.add(result);
			}

		} catch (SQLException e) {
			return null;
		}
		
		Collections.sort(results, new Comparator<Result>() {
			public int compare(Result arg0, Result arg1) {
				if(arg0.getScore() != arg1.getScore()) return arg1.getScore() - arg0.getScore();
				return arg0.getTime() - arg1.getTime();
			}
		});

		return results;
	}

	public ArrayList<Result> getRecentResultsForQuizAndUser(int quizID, int userID, int numResults) {
		ArrayList<Result> results =  new ArrayList<Result>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results WHERE quizID=" + quizID + " AND userID=" + userID + " ORDER BY date_taken DESC LIMIT 0, " + numResults);

			if (!rs.isBeforeFirst() ) {    
				return results;
			} 
			
			while(rs.next()) {
				Result result = createResult(rs);
				if(!result.wasPractice()) results.add(result);
			}

		} catch(SQLException e) {
		}

		return results;
	}


	public ArrayList<Result> getRecentResultsForQuiz(int quizID, int numResults) {
		ArrayList<Result> results =  new ArrayList<Result>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results WHERE quizID=" + quizID + " ORDER BY date_taken DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				Result result = createResult(rs);
				if(!result.wasPractice()) results.add(result);
			}

		} catch(SQLException e) {
		}

		return results;
	}

	public Result getAverageResultForQuiz(int quizID) {
		double score = 0;
		double time = 0;
		int numResults = 0;

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results WHERE quizID=" + quizID);

			while(rs.next()) {
				if(rs.getInt("score") > 0) {
					score += rs.getInt("score");
					time += rs.getInt("time");
					numResults++;
				}
			}

		} catch(SQLException e) {
		}

		if(numResults != 0) return new Result(0, quizID, (int)score/numResults, (int)time/numResults, false);
		else return new Result(0, quizID, 0, 0, false);
	}

	public ArrayList<Result> getFriendsRecentResults(int userID, int numResults) {
		ArrayList<Integer> friendIDs = getFriendIDs(userID);
		ArrayList<Result> results = new ArrayList<Result>();

		try {
			open();

			if(friendIDs.isEmpty()) return results;

			String query = "SELECT * FROM results WHERE userID IN (";
			for(int i = 0; i < friendIDs.size(); i++) {
				query += friendIDs.get(i) + ", ";
			}

			query = query.substring(0, query.length() - 2);
			query += ")";

			Statement stmt2 = con.createStatement();
			stmt2.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs2 = stmt2.executeQuery(query + " LIMIT 0, " + numResults);

			while(rs2.next()) {
				Result result = createResult(rs2);
				if(!result.wasPractice()) results.add(result);
			}


		} catch (SQLException e) {
			 
		}

		return results;
	}

	public ArrayList<Achievement> getFriendsRecentAchievements(int userID, int num) {
		ArrayList<Integer> friendIDs = getFriendIDs(userID);
		HashSet<Achievement> achievements = new HashSet<Achievement>();

		try {
			open();

			if(friendIDs.isEmpty()) return new ArrayList<Achievement>();

			String query = "SELECT * FROM achievements WHERE userID IN (";
			for(int i = 0; i < friendIDs.size(); i++) {
				query += friendIDs.get(i) + ", ";
			}

			query = query.substring(0, query.length() - 2);
			query += ")";

			Statement stmt2 = con.createStatement();
			stmt2.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs2 = stmt2.executeQuery(query);

			while(rs2.next()) {
				achievements.add(createAchievement(rs2));
			}


		} catch (SQLException e) {
		}

		return new ArrayList<Achievement>();
	}

	public ArrayList<Quiz> getFriendsRecentlyCreatedQuizzes(int userID, int numResults) {
		ArrayList<Integer> friendIDs = getFriendIDs(userID);
		ArrayList<Quiz> quizzes = new ArrayList<Quiz>();

		try {
			open();

			if(friendIDs.isEmpty()) return quizzes;

			String query = "SELECT * FROM quizzes WHERE creatorID IN (";
			for(int i = 0; i < friendIDs.size(); i++) {
				query += friendIDs.get(i) + ", ";
			}

			query = query.substring(0, query.length() - 2);
			query += ") ORDER BY date_created DESC";

			Statement stmt2 = con.createStatement();
			stmt2.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs2 = stmt2.executeQuery(query);

			while(rs2.next()) {
				quizzes.add(createQuiz(rs2));
			}


		} catch (SQLException e) {
		}

		return quizzes;
	}

	public ArrayList<Achievement> getRecentAchievements(int numResults) {
		ArrayList<Achievement> achievements =  new ArrayList<Achievement>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM achievements ORDER BY date_taken DESC LIMIT 0, " + numResults);

			while(rs.next()) {
				achievements.add(createAchievement(rs));
			}

		} catch(SQLException e) {
			 
		}

		return achievements;
	}

	public boolean userTookPractice(int userID) {

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM results WHERE userID=" + userID);

			while(rs.next()) {
				if(createResult(rs).wasPractice()) return true;
			}


		} catch (SQLException e) {
			return false;
		}

		return false;
	}

	public int getTotalNumUsers() {
		int numUsers = 0;
		try {
			open();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM accounts");

			rs.next();
			numUsers = rs.getInt(1);
		} catch (SQLException e) {
			 
		}
		return numUsers - 1; //account for anonymous account
	}

	public int getTotalNumQuizzes() {
		int numQuizzes = 0;
		try {
			open();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM quizzes");

			rs.next();
			numQuizzes = rs.getInt(1);
		} catch (SQLException e) {
			 
		}
		return numQuizzes;
	}

	public int getTotalNumQuizzesTaken() {
		int numResults = 0;
		try {
			open();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM results");

			rs.next();
			numResults = rs.getInt(1);
		} catch (SQLException e) {
			 
		}
		return numResults;
	}

	private ArrayList<Integer> getFriendIDs(int userID) {
		ArrayList<Integer> friendIDs = new ArrayList<Integer>();

		try {
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = stmt.executeQuery("SELECT * FROM friends WHERE userID=" + userID + " OR friendID=" + userID);

			while(rs.next()) {
				if(rs.getInt("userID") != userID && !friendIDs.contains(rs.getInt("userID"))) friendIDs.add(rs.getInt("userID"));
				if(rs.getInt("friendID") != userID && !friendIDs.contains(rs.getInt("friendID"))) friendIDs.add(rs.getInt("friendID"));
			}

		} catch (SQLException e) {
			 
		}

		return friendIDs;
	}

	/* =============== GETTING FUNCTIONS =============== */




	/* =============== STORING FUNCTIONS =============== */




	/**
	 * Stores an Account in the database
	 * @param Account that you want to store
	 */
	public void storeAccount(Account account) {
		try {	
			open();

			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + MyDBInfo.MYSQL_DATABASE_NAME +
					".accounts (name, username, password_hash, is_admin) VALUES (?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

			stmt.setString(1, account.getName());
			stmt.setString(2, account.getUsername());
			stmt.setString(3, account.getHashedPassword());
			stmt.setBoolean(4, account.isAdmin());

			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			account.setID(rs.getInt(1)); //gets key from resultset and stores into account's id
			
		} catch (SQLException e) {
		}
	}

	/**
	 *  Stores a Quiz in the database with all associated Questions and Answers
	 * @param quiz
	 */
	public void storeQuiz(Quiz quiz) {
		try {	
			open();
			PreparedStatement stmt = con.prepareStatement(" INSERT INTO  " + MyDBInfo.MYSQL_DATABASE_NAME +
					".quizzes (name, description, is_ordered, is_singlePage, has_practiceMode, immediate_correction, visible_to_public, creatorID, date_created) VALUES (?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

			stmt.setString(1, quiz.getName());
			stmt.setString(2, quiz.getDescription());
			stmt.setBoolean(3, quiz.is_ordered());
			stmt.setBoolean(4, quiz.is_singlePage());
			stmt.setBoolean(5, quiz.has_practiceMode());
			stmt.setBoolean(6, quiz.should_correctImmediately());
			stmt.setBoolean(7, quiz.isVisible_to_public());
			stmt.setInt(8, quiz.getCreator().getID());
			stmt.setTimestamp(9, new Timestamp(quiz.getDate_created().getTime()));

			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			quiz.setID(rs.getInt(1)); //gets key from resultset and stores into quizz's id


		} catch (SQLException e) {
		}
	}

	/**
	 * Stores a Question in the database with all associated Answers
	 * @param Question that you want to store
	 */
	public void storeQuestion(Question question) {
		try {	
			open();

			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + MyDBInfo.MYSQL_DATABASE_NAME + 
					".questions (quizID, content, type, time, priority, weight) VALUES (?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

			stmt.setInt(1, question.getQuiz().getID());
			stmt.setString(2, question.getContent());
			stmt.setInt(3, question.getType());
			stmt.setInt(4, question.getTime());
			stmt.setInt(5, question.getPriority());
			stmt.setInt(6, question.getWeight());

			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			question.setID(rs.getInt(1)); //gets key from resultset and stores into question's id


		} catch (SQLException e) {
		}
	}

	/**
	 * Stores a message in the database
	 * @param Message that you want to store
	 */
	public void storeMessage(Message message) {
		try {	
			open();

			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + MyDBInfo.MYSQL_DATABASE_NAME + 
					".messages (fromID, toID, type, content, is_read, quizID, time_sent) VALUES (?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

			stmt.setInt(1, message.getSender().getID());
			stmt.setInt(2, message.getRecipient().getID());
			stmt.setInt(3, message.getType());
			stmt.setString(4, message.getContent());
			stmt.setBoolean(5, message.has_been_read());
			if(message.getChallengedQuiz() == null) stmt.setInt(6, -1);
			else stmt.setInt(6, message.getChallengedQuiz().getID());
			stmt.setTimestamp(7, new Timestamp(message.getTimeSent().getTime()));

			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			message.setID(rs.getInt(1)); //gets key from resultset and stores into message's id


		} catch (SQLException e) {
		}
	}

	/**
	 * Stores an answer in the database
	 * @param Answer you want to store in the database
	 */
	public void storeAnswer(Answer answer) {
		try {	
			open();
			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + MyDBInfo.MYSQL_DATABASE_NAME + 
					".answers (questionID, content, type, is_correct, priority) VALUES (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
			stmt.setInt(1, answer.getQuestion().getID());
			stmt.setString(2, answer.getContent());
			stmt.setInt(3, answer.getType());
			stmt.setBoolean(4, answer.isCorrect());
			stmt.setInt(5, answer.getPriority());
			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			answer.setID(rs.getInt(1)); //gets key from resultset and stores into answer's id
		} catch (SQLException e) {
		}	
	}

	/**
	 * Stores an Achievement in the database
	 * @param Achievement you want to store in the database
	 */
	public void storeAchievement(Achievement achievement) {
		try {	
			open();

			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + MyDBInfo.MYSQL_DATABASE_NAME + 
					".achievements (achievementID, userID) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);

			stmt.setInt(1, achievement.getAchievementID());
			stmt.setInt(2, achievement.getUserID());

			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			achievement.setID(rs.getInt(1)); //gets key from resultset and stores into achievement's id


		} catch (SQLException e) {
		}
	}	


	/**
	 * Stores a Result in the database
	 * @param Result you want to store in the database
	 */
	public void storeResult(Result result) {
		try {	
			open();

			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + MyDBInfo.MYSQL_DATABASE_NAME + 
					".results (userID, quizID, score, time, date_taken, practice) VALUES (?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

			stmt.setInt(1, result.getUser().getID());
			stmt.setInt(2, result.getQuiz().getID());
			stmt.setInt(3, result.getScore());
			stmt.setInt(4, result.getTime());
			stmt.setTimestamp(5, new Timestamp(result.getDate_taken().getTime()));
			stmt.setBoolean(6, result.wasPractice());

			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			result.setID(rs.getInt(1)); //gets key from resultset and stores into result's id


		} catch (SQLException e) {
		}
	}


	/**
	 * Stores a 'friendship in the database.
	 * NOTE: stores a two-way relationship in the database
	 * @param user- int id of sending friend
	 * @param friend- int id  of receiving friend
	 */
	public void storeFriend(int userID, int friendID) {
		try {
			open();

			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + MyDBInfo.MYSQL_DATABASE_NAME + ".friends (userID, friendID) VALUES (?,?)");

			stmt.setInt(1, userID);
			stmt.setInt(2, friendID);

			stmt.executeUpdate();

		} catch (SQLException e) {
		}
	}

	/**
	 * Stores the tag in the database
	 * NOTE: assumes a trimmed lowercase tag
	 * @param quizID - id of quiz you want to store the tag for
	 * @param tag - string representation of tag. Must be trimmed and lowercase
	 */
	public void storeTag(int quizID, String tag) {
		try {
			open();

			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + MyDBInfo.MYSQL_DATABASE_NAME + ".tags (quizID, tag) VALUES (?,?)");

			stmt.setInt(1, quizID);
			stmt.setString(2, tag);

			stmt.executeUpdate();

		} catch (SQLException e) {
		}
	}



	/* =============== STORING FUNCTIONS =============== */



	/* =============== DELETE FUNCTIONS =============== */




	/**
	 * Deletes account in database with specified id number.
	 * 	-Deletes account
	 *  -Deletes all quizzes created by user
	 * 	-Deletes all messages associated with Account
	 * 	-Removes account from all friends lists
	 * 	-Removes account from Achievements list
	 * 	-NOTE: keeps account on achievements list
	 * @param param - int id of the account you want to delete
	 * @return boolean deleted - returns true if deletion was successful
	 */
	public void deleteAccount(int id) { //if we delete account, who will be quizz's creator?
		try {
			open();

			Statement quizStmt = con.createStatement();
			quizStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			ResultSet rs = quizStmt.executeQuery("SELECT id FROM quizzes WHERE creatorID=" + id);
			while(rs.next()) deleteQuiz(rs.getInt("id")); //delete all quizzes

			Statement messageStmt = con.createStatement();
			messageStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			messageStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".messages WHERE (fromID=" + id + " OR toID=" + id+")"); //delete all messages

			Statement friendStmt = con.createStatement();
			friendStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			friendStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".friends  WHERE (userID=" + id +" OR friendID=" + id+")");
			//delete all friendships
			
			Statement resultStmt = con.createStatement();
			resultStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			resultStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".results WHERE userID=" + id);

			Statement achievementStmt = con.createStatement();
			achievementStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			achievementStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".achievements WHERE userID=" + id); //delete all achievements

			Statement accountStmt = con.createStatement();
			accountStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			accountStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".accounts WHERE id=" + id); //delete account

		} catch (SQLException e) {
		}
	}

	/**
	 * Deletes a quiz from the database
	 * 	-deletes all questions and associated answers
	 *  -removes all resuls
	 * @param id - int id of Quiz to be deleted
	 */
	public void deleteQuiz(int id) {
		try {
			open();

			ArrayList<Question> questions = getQuizQuestions(id);

			for(int i = 0; i < questions.size(); i++) {
				deleteQuestion(questions.get(i).getID());
			}

			Statement resultStmt = con.createStatement();
			resultStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			resultStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".results WHERE quizID=" + id);

			Statement quizStmt = con.createStatement();
			quizStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			quizStmt.executeUpdate("DELETE FROM quizzes WHERE id=" + id);
			
			Statement tagStmt = con.createStatement();
			tagStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			tagStmt.executeUpdate("DELETE FROM tags WHERE quizID=" + id);
			
			Statement msgStmt = con.createStatement();
			msgStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			msgStmt.executeUpdate("DELETE FROM messages WHERE quizID=" + id);

		} catch (SQLException e) {
		}
	}

	/**
	 * Deletes a question from the database
	 * @param id - int id of Question to be deleted
	 */
	public void deleteQuestion(int id) {
		try {
			open();

			Statement answerStmt = con.createStatement();
			answerStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			answerStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".answers WHERE questionID=" + id);;

			Statement questionStmt = con.createStatement();
			questionStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			questionStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".questions WHERE id=" + id);

		} catch (SQLException e) {
		}
	}

	/**
	 * Deletes an answer from the database
	 * @param id - int id of Answer to be deleted
	 */
	public void deleteAnswer(int id) {
		try {	
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			stmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".answers WHERE id=" + id);

		} catch (SQLException e) {
		}
	}

	/**
	 * Deletes a message from the database
	 * @param id - int id of message to be deleted
	 */
	public void deleteMessage(int id) {
		try {	
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			stmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".messages WHERE id=" + id);


		} catch (SQLException e) {
		}
	}

	/**
	 * Removes friendship in database (both ways)
	 * @param userID - int id of friend (Account) to be unfriended
	 * @param friendID - int id of other friend (Account) to be unfriended
	 */
	public void unfriend(int userID, int friendID) {
		try {	
			open();
			
			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			stmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME +
					".friends WHERE (userID=" + userID + " AND friendID=" + friendID + ") OR (userID=" + friendID + " AND friendID=" + userID + ")");

		} catch (SQLException e) {
		}
	}

	/**
	 * Deletes a certain tag for the quiz
	 * @param quizID - id of quiz you want to delete tag for
	 * @param tag - tag that you want to delete. Must be lowercased and trimmed
	 */
	public void deleteTag(int quizID, String tag) {
		try {	
			open();

			Statement stmt = con.createStatement();
			stmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
			stmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".tags WHERE quizID=" + quizID + " AND tag=\"" + tag+ "\"");


		} catch (SQLException e) {
		}
	}
	
	public void deleteQuizHistory(int quizID) {
		try {
		Statement resultStmt = con.createStatement();
		resultStmt.executeQuery("USE " + MyDBInfo.MYSQL_DATABASE_NAME);
		resultStmt.executeUpdate("DELETE FROM " + MyDBInfo.MYSQL_DATABASE_NAME + ".results WHERE quizID=" + quizID);
		} catch(SQLException e) {
		}
	}



	/* =============== DELETE FUNCTIONS =============== */



	/* =============== EDIT FUNCTIONS =============== */






	/**
	 * Takes in a Quiz object and updates it in the database.
	 * Proactively updates all associated Questions and Answers, too.
	 * @param quiz - Quiz object to be updated in database
	 */
	public void editQuiz(Quiz quiz) {
		try {	
			open();

			PreparedStatement stmt = con.prepareStatement("UPDATE " + MyDBInfo.MYSQL_DATABASE_NAME + 
			".quizzes SET name=?, description=?, is_ordered=?, is_singlePage=?, has_practiceMode=?, immediate_correction=?, visible_to_public=?, creatorID=?, date_created=? WHERE id=?");

			stmt.setString(1, quiz.getName());
			stmt.setString(2, quiz.getDescription());
			stmt.setBoolean(3, quiz.is_ordered());
			stmt.setBoolean(4, quiz.is_singlePage());
			stmt.setBoolean(5, quiz.has_practiceMode());
			stmt.setBoolean(6, quiz.should_correctImmediately());
			stmt.setBoolean(7, quiz.isVisible_to_public());
			stmt.setInt(8, quiz.getCreator().getID());
			stmt.setTimestamp(9, new Timestamp(quiz.getDate_created().getTime()));
			stmt.setInt(10, quiz.getID());

			stmt.executeUpdate();

		} catch (SQLException e) {
		}
	}

	/**
	 * Takes in a Question object and updates it in the database.
	 * Proactively updates all Answer objects associated with Question, too.
	 * @param question - Question object to be updated in database
	 */
	public void editQuestion(Question question) {
		try {	
			open();

			PreparedStatement stmt = con.prepareStatement("UPDATE " + MyDBInfo.MYSQL_DATABASE_NAME + 
			".questions SET content=?, type=?, time=?, priority=?, weight=? WHERE id=?");

			stmt.setString(1, question.getContent());
			stmt.setInt(2, question.getType());
			stmt.setInt(3, question.getTime());
			stmt.setInt(4, question.getPriority());
			stmt.setInt(5, question.getWeight());
			stmt.setInt(6, question.getID());

			stmt.executeUpdate();
		} catch (SQLException e) {
		}
	}
	/**
	 * Takes in an Answer object and updates it in the database.
	 * @param answer - Answer object to be updated in database.
	 */
	public void editAnswer(Answer answer) {
		try {	
			open();

			PreparedStatement stmt = con.prepareStatement("UPDATE " + MyDBInfo.MYSQL_DATABASE_NAME +
			".answers SET questionID=?, content=?, type=?, is_correct=?, priority=? WHERE id=?");

			stmt.setInt(1, answer.getQuestion().getID());
			stmt.setString(2, answer.getContent());
			stmt.setInt(3, answer.getType());
			stmt.setBoolean(4, answer.isCorrect());
			stmt.setInt(5, answer.getPriority());
			stmt.setInt(6, answer.getID());

			stmt.executeUpdate();


		} catch (SQLException e) {
		}
	}

	/**
	 * Takes in an Account object and updates it in the database.
	 * @param account - Account object to be updated in database
	 */
	public void editAccount(Account account) {
		try {		
			open();

			PreparedStatement stmt = con.prepareStatement("UPDATE " + MyDBInfo.MYSQL_DATABASE_NAME +
			".accounts SET name=?, username=?, password_hash=?, is_admin=? WHERE id=?");

			stmt.setString(1, account.getName());
			stmt.setString(2, account.getUsername());
			stmt.setString(3, account.getHashedPassword());
			stmt.setBoolean(4, account.isAdmin());
			stmt.setInt(5, account.getID());

			stmt.executeUpdate();

		} catch (SQLException e) {
		}
	}
	
	public void editMessage(Message message) {
		try {		
			open();

			PreparedStatement stmt = con.prepareStatement("UPDATE " + MyDBInfo.MYSQL_DATABASE_NAME +
			".messages SET fromID=?, toID=?, type=?, content=?, is_read=?, quizID=?, time_sent=? WHERE id=?");

			stmt.setInt(1, message.getSender().getID());
			stmt.setInt(2, message.getRecipient().getID());
			stmt.setInt(3, message.getType());
			stmt.setString(4, message.getContent());
			stmt.setBoolean(5, message.has_been_read());
			if(message.getChallengedQuiz() == null) stmt.setInt(6, -1);
			else stmt.setInt(6, message.getChallengedQuiz().getID());
			stmt.setTimestamp(7, new Timestamp(message.getTimeSent().getTime()));
			stmt.setInt(8, message.getID());

			stmt.executeUpdate();

		} catch (SQLException e) {
		}
	}



	/* =============== EDIT FUNCTIONS =============== */


	/* =============== OVERWRITTEN FUNCTIONS =============== */




	/**
	 * Deletes a message from the database
	 * @param id - Message object itself to be deleted
	 */
	public void deleteMessage(Message message) {
		deleteMessage(message.getID());
	}

	/**
	 * Removes friendship in database (both ways)
	 * @param userID - friend (Account) to be unfriended
	 * @param friendID - friend (Account) to be unfriended
	 */
	public void unfriend(Account user, Account friend) {
		unfriend(user.getID(), friend.getID());
	}

	/**
	 * Deletes a question from the database
	 * @param id - Question itself to be deleted
	 */
	public void deleteQuestion(Question question) {
		deleteQuestion(question.getID());
	}

	/**
	 * Deletes a quiz from the database
	 * @param id - Quiz object itself to be deleted
	 */
	public void deleteQuiz(Quiz quiz) {
		deleteQuiz(quiz.getID());
	}

	/**
	 * Stores a 'friendship in the database.
	 * NOTE: stores a two-way relationship in the database
	 * @param user- Account of sending friend
	 * @param friend- Account of receiving friend
	 */
	public void storeFriend(Account user, Account friend) {
		storeFriend(user.getID(), friend.getID());
	}

	/**
	 * Gets a number of global recent Results for a specific quiz.
	 * If numResults is < 0, throws an exception
	 * If numResults < number of Results in database, it will return all the results in the database
	 * @param userID - Quiz object itself which is desired to get results for
	 * @return ArrayList of Results, empty if there are no results, null if sql failed
	 */
	public ArrayList<Result> getTopQuizResults(Quiz quiz, int numResults) {
		return getTopQuizResults(quiz.getID(), numResults);
	}

	/**
	 * Gets an arraylist of quizzes given an Account user
	 * @param Account -- account of user desired
	 * @return arraylist of quizzes that are owned by the user
	 */
	public ArrayList<Quiz> getQuizzesByUser(Account user) {
		return getQuizzesByUser(user.getID());
	}

	/**
	 * Gets all the Friends a User has, given a userID number or Account.
	 * Returns the friends (Accounts) in a ArrayList
	 * @param userID - Account object itself which is desired to get friends from
	 * @return ArrayList of Accounts, empty if there are no friends, null if sql failed
	 */
	public ArrayList<Account> getFriends(Account user) {
		return getFriends(user.getID());
	}


	/**
	 * Gets an ArrayList of messages sent from a certain user
	 * @param user - Account object itself that you want to get messages from
	 * @return ArrayList of Messages sent from given user
	 */
	public ArrayList<Message> getUserSentMessages(Account user) {
		return getUserSentMessages(user.getID());
	}
	
	/**
	 * Gets an ArrayList of messages received by a certain user
	 * @param user - Account object itself that you want to get messages from
	 * @return ArrayList of Messages received by given user
	 */
	public ArrayList<Message> getUserReceivedtMessages(Account user) {
		return getUserReceivedMessages(user.getID());
	}

	/**
	 * Gets all the Achievements a User has, given a userID number.
	 * Returns the achievements in a ArrayList
	 * @param userID - desired Account object itself for which you want the achievements
	 * @return ArrayList of Achievements, empty if there are no achievements, null if sql failed
	 */
	public ArrayList<Achievement> getUserAchievements(Account user) {
		return getUserAchievements(user.getID());
	}

	/**
	 * Gets all the questions associated with a quiz
	 * @param quiz - quiz object of desired quiz you want to get the questions from
	 * @return A sortable ArrayList of Questions of that specified quiz
	 */
	public ArrayList<Question> getQuizQuestions(Quiz quiz) {
		return getQuizQuestions(quiz.getID());
	}

	/**
	 * Gets a sortable ArrrayList of answers for a given question
	 * @param question -- Question object itself
	 * @return ArrayList of Answers that consists of all the answers for the given question
	 */
	public ArrayList<Answer> getAnswersForQuestion(Question question) {
		return getAnswersForQuestion(question.getID());
	}

	/**
	 * Deletes account in database with specified id number.
	 * 	-Deletes account
	 * 	-Deletes all messages associated with Account
	 * 	-Removes account from all friends lists
	 * 	-Removes account from Achievements list
	 * 	-NOTE: keeps account on achievements list
	 * @param param - Account object of the account you want to delete
	 * @return boolean deleted - returns true if deletion was successful
	 */
	public void deleteAccount(Account account) {
		deleteAccount(account.getID());
	}

	/**
	 * Gets a number of Results of quizzes for a user. If numResults is < 0, throws an exception
	 * If numResults < number of Results in database, it will return all the results in the database
	 * @param user - Account desired to get results for
	 * @return ArrayList of Results, empty if there are no results, null if sql failed
	 */
	public ArrayList<Result> getRecentResultsForUser(Account user, int numResults) {
		return getRecentResultsForUser(user.getID(), numResults);
	}

	private Account createAccount(ResultSet rs) {
		try {
			int ID = rs.getInt("id");
			Account account = new Account(rs.getString("name"), rs.getString("username"), rs.getString("password_hash"), rs.getBoolean("is_admin"));
			account.setID(ID);
			return account;
		} catch (SQLException ignored) { }

		return null;
	}

	private Quiz createQuiz(ResultSet rs) {
		try {
			Timestamp t = rs.getTimestamp("date_created");
			int ID = rs.getInt(1);
			Quiz quiz = new Quiz(rs.getString("name"), rs.getString("description"), rs.getBoolean("is_ordered"), rs.getBoolean("is_singlePage"), rs.getBoolean("has_practiceMode"), rs.getBoolean("immediate_correction"), rs.getBoolean("visible_to_public"), rs.getInt("creatorID"));
			quiz.setDate_created(t);
			quiz.setID(ID);
			return quiz;
		} catch (SQLException ignored) { }

		return null;
	}

	private Result createResult(ResultSet rs) {
		try {
			Timestamp t = rs.getTimestamp("date_taken");
			int ID = rs.getInt("id");
			Result result = new Result(rs.getInt("userID"), rs.getInt("quizID"), rs.getInt("score"), rs.getInt("time"), rs.getBoolean("practice"));
			result.setDate_taken(t);
			result.setID(ID);
			return result;
		} catch (SQLException ignored) { }

		return null;
	}

	private Question createQuestion(ResultSet rs) {
		try {
			int ID = rs.getInt("id");
			Question question = new Question(rs.getInt("quizID"), rs.getString("content"), rs.getInt("type"), rs.getInt("time"), rs.getInt("priority"), rs.getInt("weight"));
			question.setID(ID);
			return question;
		} catch (SQLException ignored) { }

		return null;
	}

	private Answer createAnswer(ResultSet rs) {
		try {
			int ID = rs.getInt("id");
			Answer answer = new Answer(rs.getInt("questionID"), rs.getString("content"), rs.getInt("type"), rs.getBoolean("is_correct"), rs.getInt("priority"));
			answer.setID(ID);
			return answer;
		} catch (SQLException ignored) { }

		return null;
	}

	private Message createMessage(ResultSet rs) {
		try {
			Timestamp t = rs.getTimestamp("time_sent");
			int ID =rs.getInt("id");
			Message message = new Message(rs.getInt("fromID"), rs.getInt("toID"), rs.getInt("type"), rs.getString("content"), rs.getBoolean("is_read"), rs.getInt("quizID"));
			message.setTimeSent(t);
			message.setID(ID);
			return message;
		} catch (SQLException ignored) { }

		return null;
	}

	private Achievement createAchievement(ResultSet rs) {
		try {
			int ID = rs.getInt("id");
			Achievement achievement = new Achievement(rs.getInt("userID"), rs.getInt("achievementID"));
			achievement.setID(ID);
			return achievement;
		} catch (SQLException ignored) { }

		return null;
	}

}
